import pymssql
from dbutils.persistent_db import PersistentDB

# 用户类
class User2:
    def __init__(self, id, user_name, password):
        self.id = id,
        self.user_name = user_name
        self.password = password

    def __repr__(self):
        return f"User2({self.id}, {self.user_name}, {self.password})"

# 一级学科类，用于封装一级学科表的数据
class PrimarySubject:
    def __init__(self, id, name, description, total_num, supply_num, type):
        self.id = id
        self.name = name
        self.description = description
        self.total_num = total_num
        self.supply_num = supply_num
        self.type = type

    def __repr__(self):
        return f"PrimarySubject({self.id}, {self.name}, {self.description}, {self.total_num}, {self.supply_num}, {self.type})"

# 二级学科类，用于封装二级学科表的数据
class SecondarySubject:
    def __init__(self, id, name, parent_id, description, type, initial_subject1, initial_subject2, initial_subject3,
                 initial_subject4, interview_subject1, interview_subject2, tutors):
        self.id = id
        self.name = name
        self.parent_id = parent_id
        self.description = description
        self.type = type
        self.initial_subject1 = initial_subject1
        self.initial_subject2 = initial_subject2
        self.initial_subject3 = initial_subject3
        self.initial_subject4 = initial_subject4
        self.interview_subject1 = interview_subject1
        self.interview_subject2 = interview_subject2
        self.tutors = tutors

    def __repr__(self):
        return f"SecondarySubject({self.id}, {self.name}, {self.parent_id}, {self.description}, {self.type}, " \
               f"{self.initial_subject1}, {self.initial_subject2}, {self.initial_subject3}, {self.initial_subject4}, " \
               f"{self.interview_subject1}, {self.interview_subject2}, {self.tutors})"

# 导师类，用于封装导师表的数据
class Tutor:
    def __init__(self, id, name, title, photo, introduction, email, phone, eligibility):
        self.id = id
        self.name = name
        self.title = title
        self.photo = photo
        self.introduction = introduction
        self.email = email
        self.phone = phone
        self.eligibility = eligibility

    def __repr__(self):
        return f"Tutor({self.id}, {self.name}, {self.title}, {self.photo}, {self.introduction}, {self.email}, " \
               f"{self.phone}, {self.eligibility})"

# 学生类，用于封装学生表的数据
class Student:
    def __init__(self, id, name, birth, idnumber, birth_place, undergraduate_major, email, phone, undergraduate_institution,
                 institution_type, gender, resume, subject_id, subject_name):
        self.id = id
        self.name = name
        self.birth = birth
        self.idnumber = idnumber
        self.birth_place = birth_place
        self.undergraduate_major = undergraduate_major
        self.email = email
        self.phone = phone
        self.undergraduate_institution = undergraduate_institution
        self.institution_type = institution_type
        self.gender = gender
        self.resume = resume
        self.subject_id = subject_id
        self.subject_name = subject_name

    def __repr__(self):
        return f"Student({self.id}, {self.name}, {self.birth}, {self.idnumber}, {self.birth_place}, " \
               f"{self.undergraduate_major}, {self.email}, {self.phone}, {self.undergraduate_institution}, " \
               f"{self.institution_type}, {self.gender}, {self.resume}, {self.subject_id}, {self.subject_name})"

# 志愿类，用于封装志愿表的数据
class Choice:
    def __init__(self, id, student_id, student_name=None ,first_choice_instructor=None, first_choice_instructor_name=None,
                 second_choice_instructor=None, second_choice_instructor_name=None, third_choice_instructor=None, third_choice_instructor_name=None):
        self.id = id
        self.student_id = student_id
        self.student_name = student_name
        self.first_choice_instructor = first_choice_instructor
        self.first_choice_instructor_name = first_choice_instructor_name
        self.second_choice_instructor = second_choice_instructor
        self.second_choice_instructor_name = second_choice_instructor_name
        self.third_choice_instructor = third_choice_instructor
        self.third_choice_instructor_name = third_choice_instructor_name

    def __repr__(self):
        return f"Choice({self.id}, {self.student_id}, {self.student_name}, {self.first_choice_instructor}, " \
               f"{self.first_choice_instructor_name}, {self.second_choice_instructor}, {self.second_choice_instructor_name}" \
               f"{self.third_choice_instructor}), {self.third_choice_instructor_name}"

# 成绩类，用于封装成绩表的数据
class Score:
    def __init__(self, id, student_id, initial_score1, initial_score2, initial_score3, initial_score4, interview_subject1,
                 interview_subject2):
        self.id = id
        self.student_id = student_id
        self.initial_score1 = initial_score1
        self.initial_score2 = initial_score2
        self.initial_score3 = initial_score3
        self.initial_score4 = initial_score4
        self.interview_subject1 = interview_subject1
        self.interview_subject2 = interview_subject2

    def __repr__(self):
        return f"Score({self.id}, {self.student_id}, {self.initial_score1}, {self.initial_score2}, {self.initial_score3}, {self.initial_score4}, " \
               f"{self.interview_subject1}, {self.interview_subject2})"

# 成绩类，用于封装成绩表的数据
class Result:
    def __init__(self, student_id, student_name ,tutor_id):

        self.student_id = student_id
        self.student_name = student_name
        self.tutor_id = tutor_id

    def __repr__(self):
        return f"Result({self.student_id}, {self.student_name}, {self.tutor_id})"

# DAO可扩展连接类
class DAOConnection:
    _instance = None

    def __new__(cls, *args, **kwargs):
        if not cls._instance:
            cls._instance = super().__new__(cls)
            print('单例')
        return cls._instance

    def __init__(self, server, user, password, database):
        self.server = server
        self.user = user
        self.password = password
        self.database = database
        self.connection = None
        self.cursor = None

    # 连接
    def connect(self):
        try:
            POOL = PersistentDB(
                creator=pymssql,  # 使用链接数据库的模块
                maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
                setsession=[],  # 开始会话前执行的命令列表。
                ping=0,
                # ping 服务端，检查是否服务可用。
                closeable=False,
                # 如果为False时，conn.close()实际上被忽略，供下次使用，再线程关闭时，才会自动关闭链接。如果为True时，conn.close()则关闭链接，那么再次调用pool.connection时就会报错，因为已经真的关闭了连接（pool.steady_connection()可以获取一个新的链接）
                threadlocal=None,  # 本线程独享值得对象，用于保存链接对象，如果链接对象被重置
                host=self.server,
                user=self.user,
                password=self.password,
                database=self.database
            )
            self.connection = POOL.connection()
            self.cursor = self.connection.cursor()
            print('连接数据库')
        except pymssql.Error as e:
            print("Error connecting to SQL Server:", e)

    # 断开连接
    def close(self):
        if self.cursor:
            try:
                self.cursor.close()
            except pymssql.Error as e:
                print("Error closing cursor:", e)
        if self.connection:
            try:
                self.connection.close()
            except pymssql.Error as e:
                print("Error closing connection:", e)

# 一级学科DAO类
class PrimarySubjectDAO:
    def __init__(self, connection):
        self.connection = connection

    def insert(self, primary_subject):
        query = "INSERT INTO primary_subject (id, name, description, total_num, supply_num, type) VALUES (%s, %s, %s, %s, %s, %s)"
        values = (primary_subject.id, primary_subject.name, primary_subject.description, primary_subject.total_num,
                  primary_subject.supply_num, primary_subject.type)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error inserting primary subject data:", e)

    def update(self, primary_subject):
        query = "UPDATE primary_subject SET name = %s, description = %s, total_num = %s, supply_num = %s, type = %s WHERE id = %s"
        values = (primary_subject.name, primary_subject.description, primary_subject.total_num,
                  primary_subject.supply_num, primary_subject.type, primary_subject.id)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error updating primary subject data:", e)

    def delete(self, id):
        query = "DELETE FROM primary_subject WHERE id = %s"
        try:
            self.connection.cursor.execute(query, (id,))
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error deleting primary subject data:", e)

    def find_by_id(self, id):
        query = "SELECT * FROM primary_subject WHERE id = %s"
        try:
            self.connection.cursor.execute(query, (id,))
            result = self.connection.cursor.fetchone()
            if result:
                return PrimarySubject(result[0], result[1], result[2], result[3], result[4], result[5])
            return None
        except pymssql.Error as e:
            print("Error finding primary subject by id:", e)

    def find_all(self):
        query = "SELECT * FROM primary_subject"
        try:
            self.connection.cursor.execute(query)
            results = self.connection.cursor.fetchall()
            primary_subjects = []
            for result in results:
                primary_subjects.append(
                    PrimarySubject(result[0], result[1], result[2], result[3], result[4], result[5]))
            return primary_subjects
        except pymssql.Error as e:
            print("Error finding all primary subjects:", e)

# 二级学科DAO类
class SecondarySubjectDAO:
    def __init__(self, connection):
        self.connection = connection

    def insert(self, secondary_subject):
        query = "INSERT INTO secondary_subject (id, name, parent_id, description, type, initial_subject1, " \
                "initial_subject2, initial_subject3, initial_subject4, interview_subject1, interview_subject2) " \
                "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        values = (secondary_subject.id, secondary_subject.name, secondary_subject.parent_id,
                  secondary_subject.description, secondary_subject.type, secondary_subject.initial_subject1,
                  secondary_subject.initial_subject2, secondary_subject.initial_subject3, secondary_subject.initial_subject4,
                  secondary_subject.interview_subject1, secondary_subject.interview_subject2)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error inserting secondary subject data:", e)

    def update(self, secondary_subject):
        query = "UPDATE secondary_subject SET name = %s, parent_id = %s, description = %s, type = %s, " \
                "initial_subject1 = %s, initial_subject2 = %s, initial_subject3 = %s, initial_subject4 = %s, " \
                "interview_subject1 = %s, interview_subject2 = %s WHERE id = %s"
        values = (secondary_subject.name, secondary_subject.parent_id, secondary_subject.description,
                  secondary_subject.type, secondary_subject.initial_subject1, secondary_subject.initial_subject2,
                  secondary_subject.initial_subject3, secondary_subject.initial_subject4, secondary_subject.interview_subject1,
                  secondary_subject.interview_subject2, secondary_subject.id)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error updating secondary subject data:", e)

    def delete(self, id):
        query = "DELETE FROM secondary_subject WHERE id = %s"
        try:
            self.connection.cursor.execute(query, (id,))
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error deleting secondary subject data:", e)

    def find_by_id(self, id):
        query = "SELECT * FROM secondary_subject WHERE id = %s"
        try:
            self.connection.cursor.execute(query, (id,))
            result = self.connection.cursor.fetchone()
            if result:
                return SecondarySubject(result[0], result[1], result[2], result[3], result[4], result[5], result[6],
                                        result[7], result[8], result[9], result[10])
            return None
        except pymssql.Error as e:
            print("Error finding secondary subject by id:", e)

    def find_all(self):
        query = ("""
        SELECT s.id, s.name, s.parent_id, s.description, s.initial_subject1, s.initial_subject2, s.initial_subject3, s.initial_subject4,
               s.interview_subject1, s.interview_subject2, t.name
        FROM secondary_subject s
        LEFT JOIN tutor_subject st ON st.subject_id = s.id
        LEFT JOIN tutor t on t.id = st.tutor_id
        WHERE t.eligibility = 1;
        """)
        try:
            self.connection.cursor.execute(query)
            results = self.connection.cursor.fetchall()
            processed_data = {}
            for result in results:
                subject_id, subject_name, parent_id, description, initial_subject1, initial_subject2, initial_subject3, initial_subject4, interview_subject1, interview_subject2, tutor_name = result
                if subject_id not in processed_data:
                    processed_data[subject_id] = {
                        'id': subject_id,
                        'name': subject_name,
                        'parent_id': parent_id,
                        'description': description,
                        'initial_subject1': initial_subject1,
                        'initial_subject2': initial_subject2,
                        'initial_subject3': initial_subject3,
                        'initial_subject4': initial_subject4,
                        'interview_subject1': interview_subject1,
                        'interview_subject2': interview_subject2,
                        'tutors': [tutor_name]
                    }
                else:
                    processed_data[subject_id]['tutors'].append(tutor_name)
            result_array = list(processed_data.values())
            return  result_array
            # secondary_subjects = []
            # for result in results:
            #     secondary_subjects.append(
            #         SecondarySubject(result[0], result[1], result[2], result[3], result[4], result[5], result[6],
            #                          result[7], result[8], result[9], result[10]))
            # return secondary_subjects
        except pymssql.Error as e:
            print("Error finding all secondary subjects:", e)

# 导师DAO类
class TutorDAO:
    def __init__(self, connection):
        self.connection = connection

    def insert(self, tutor):
        query = "INSERT INTO tutor (id, name, title, photo, introduction, email, phone, eligibility) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
        values = (tutor.id, tutor.name, tutor.title, tutor.photo, tutor.introduction, tutor.email, tutor.phone,
                  tutor.eligibility)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error inserting tutor data:", e)

    def update(self, tutor):
        query = "UPDATE tutor SET name = %s, title = %s, photo = %s, introduction = %s, email = %s, phone = %s, eligibility = %s WHERE id = %s"
        values = (tutor.name, tutor.title, tutor.photo, tutor.introduction, tutor.email, tutor.phone, tutor.eligibility,
                  tutor.id)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error updating tutor data:", e)

    def delete(self, id):
        query = "DELETE FROM tutor WHERE id = %s"
        try:
            self.connection.cursor.execute(query, (id,))
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error deleting tutor data:", e)

    def find_by_id(self, id):
        query = "SELECT * FROM tutor WHERE id = %s"
        try:
            self.connection.cursor.execute(query, (id,))
            results = self.connection.cursor.fetchall()
            tutors = []
            for result in results:
                print(result)
                tutors.append(
                    Tutor(result[0], result[1], result[2], result[3], result[4], result[5], result[6], result[7]))
            return tutors
        except pymssql.Error as e:
            print("Error finding tutor by id:", e)

    def find_all(self):
        query = "SELECT * FROM tutor"
        try:
            self.connection.cursor.execute(query)
            results = self.connection.cursor.fetchall()
            tutors = []
            for result in results:
                print(result)
                tutors.append(
                    Tutor(result[0], result[1], result[2], result[3], result[4], result[5], result[6], result[7]))
            return tutors
        except pymssql.Error as e:
            print("Error finding all tutors:", e)

    def get_tutors(self, student_id):
        query = """
            select t.id, t.name from tutor t
            join tutor_subject ts on ts.tutor_id = t.id
            join student s on s.subject_id = ts.subject_id
            where s.id = %s;
        """

        try:
            self.connection.cursor.execute(query, student_id)
            results = self.connection.cursor.fetchall()
            return results
        except pymssql.Error as e:
            print("Error finding all tutors:", e)

# 学生DAO类
class StudentDAO:
    def __init__(self, connection):
        self.connection = connection

    def insert(self, student):
        query = "INSERT INTO student (id, name, birth, idnumber, birth_place, undergraduate_major, email, phone, " \
                "undergraduate_institution, institution_type, gender, resume) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        values = (student.id, student.name, student.birth, student.idnumber, student.birth_place,
                  student.undergraduate_major, student.email, student.phone, student.undergraduate_institution,
                  student.institution_type, student.gender, student.resume, student.subject_id, student.subject_name)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error inserting student data:", e)

    def update(self, student):
        query = """
            UPDATE student SET name = %s, birth = %s, idnumber = %s, birth_place = %s, undergraduate_major = %s, 
            email = %s, phone = %s, undergraduate_institution = %s, institution_type = %s, gender = %s, resume = %s, subject_id = %s WHERE id = %s
        """
        values = (student.name, student.birth, student.idnumber, student.birth_place, student.undergraduate_major,
                  student.email, student.phone, student.undergraduate_institution, student.institution_type, student.gender, student.resume, student.subject_id, student.id)
        try:
            print(student)
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
            print('修改成功')
        except pymssql.Error as e:
            print("Error updating student data:", e)

    def delete(self, id):
        query = "DELETE FROM student WHERE id = %s"
        try:
            self.connection.cursor.execute(query, (id,))
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error deleting student data:", e)

    def find_by_id(self, id):
        query = "SELECT s.*, sc.name as '报考科目' FROM student s JOIN secondary_subject sc on sc.id = s.subject_id WHERE s.id = %s"
        try:
            self.connection.cursor.execute(query, (id,))
            results = self.connection.cursor.fetchall()
            students = []
            for result in results:
                students.append(Student(result[0], result[1], result[2], result[3], result[4], result[5], result[6],
                                        result[7], result[8], result[9], result[10], result[11], result[12],
                                        result[15]))
            return students
        except pymssql.Error as e:
            print("Error finding student by id:", e)

    def find_all(self):
        query = "SELECT s.*, sc.name as '报考科目' FROM student s JOIN secondary_subject sc on sc.id = s.subject_id;"
        try:
            self.connection.cursor.execute(query)
            results = self.connection.cursor.fetchall()
            students = []
            for result in results:
                students.append(Student(result[0], result[1], result[2], result[3], result[4], result[5], result[6],
                                        result[7], result[8], result[9], result[10], result[11], result[12], result[15]))
            return students
        except pymssql.Error as e:
            print("Error finding all students:", e)

# 志愿DAO类
class ChoiceDAO:
    def __init__(self, connection):
        self.connection = connection

    def insert(self, choice):
        query = "INSERT INTO choice (id, student_id, first_choice_instructor, second_choice_instructor, third_choice_instructor) VALUES (%s, %s, %s, %s, %s)"
        values = (choice.id, choice.student_id, choice.first_choice_instructor, choice.second_choice_instructor,
                  choice.third_choice_instructor)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error inserting choice data:", e)

    def update(self, choice):
        query = "UPDATE choice SET first_choice_instructor = %s, second_choice_instructor = %s, third_choice_instructor = %s WHERE id = %s"
        values = (choice.first_choice_instructor, choice.second_choice_instructor, choice.third_choice_instructor,
                  choice.id)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error updating choice data:", e)

    def delete(self, id):
        query = "DELETE FROM choice WHERE id = %s"
        try:
            self.connection.cursor.execute(query, (id,))
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error deleting choice data:", e)

    def find_by_id(self, id):
        query = """
            SELECT c.id, s.id, s.name, c.first_choice_instructor, t1.name, c.second_choice_instructor, t2.name, c.third_choice_instructor, t3.name
            FROM choice c
            LEFT JOIN student s ON c.student_id = s.id
            LEFT JOIN tutor t1 ON c.first_choice_instructor = t1.id
            LEFT JOIN tutor t2 ON c.second_choice_instructor = t2.id
            LEFT JOIN tutor t3 ON c.third_choice_instructor = t3.id
            where c.student_id = %s;
        """
        try:
            self.connection.cursor.execute(query, id)
            results = self.connection.cursor.fetchall()
            choices = []
            for result in results:
                choices.append(
                    Choice(result[0], result[1], result[2], result[3], result[4], result[5], result[6], result[7], result[8], ))
            return choices
        except pymssql.Error as e:
            print("Error finding all choices:", e)

    def find_all(self):
        query = """
            SELECT c.id, s.id, s.name, c.first_choice_instructor, t1.name, c.second_choice_instructor, t2.name, c.third_choice_instructor, t3.name
            FROM choice c
            LEFT JOIN student s ON c.student_id = s.id
            LEFT JOIN tutor t1 ON c.first_choice_instructor = t1.id
            LEFT JOIN tutor t2 ON c.second_choice_instructor = t2.id
            LEFT JOIN tutor t3 ON c.third_choice_instructor = t3.id;
        """
        try:
            self.connection.cursor.execute(query)
            results = self.connection.cursor.fetchall()
            choices = []
            for result in results:
                choices.append(
                    Choice(result[0], result[1], result[2], result[3], result[4], result[5], result[6], result[7], result[8], ))
            return choices
        except pymssql.Error as e:
            print("Error finding all choices:", e)

    def find_tutor(self, tutor_id):
        query = """
            SELECT s.id, s.name,
                   CASE 
                       WHEN c.first_choice_instructor = %s THEN 'first'
                       WHEN c.second_choice_instructor = %s THEN 'second'
                       WHEN c.third_choice_instructor = %s THEN 'third'
                   END AS choice_rank
            FROM choice c
            JOIN student s ON s.id = c.student_id
            WHERE c.first_choice_instructor = %s OR c.second_choice_instructor = %s OR c.third_choice_instructor = %s;
        """
        try:
            self.connection.cursor.execute(query, (tutor_id, tutor_id, tutor_id, tutor_id, tutor_id, tutor_id))
            result = self.connection.cursor.fetchall()

            if result:
                print(result)
                return result
            return None
        except pymssql.Error as e:
            print("Error finding choice by id:", e)

# 成绩DAO类
class ScoreDAO:
    def __init__(self, connection):
        self.connection = connection

    def insert(self, score):
        query = "INSERT INTO score (id, student_id, initial_score1, initial_score2, initial_score3, initial_score4, interview_subject1, interview_subject2) VALUES (%s, %s, %s, %s, %s, %s, %s)"
        values = (score.id, score.student_id, score.initial_score1, score.initial_score2, score.initial_score3, score.initial_score4,
                  score.interview_subject1, score.interview_subject2)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error inserting score data:", e)

    def update(self, score):
        query = "UPDATE score SET initial_score1 = %s, initial_score2 = %s, initial_score3 = %s, initial_score4 = %s, interview_subject1 = %s, interview_subject2 = %s WHERE id = %s"
        values = (score.initial_score1, score.initial_score2, score.initial_score3, score.initial_score4, score.interview_subject1,
                  score.interview_subject2, score.id)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error updating score data:", e)

    def delete(self, id):
        query = "DELETE FROM score WHERE id = %s"
        try:
            self.connection.cursor.execute(query, (id,))
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error deleting score data:", e)

    def find_by_id(self, id):
        query = "SELECT * FROM score WHERE student_id = %s"
        try:
            self.connection.cursor.execute(query, (id,))
            results = self.connection.cursor.fetchall()
            scores = []
            for result in results:
                scores.append(
                    Score(result[0], result[1], result[2], result[3], result[4], result[5], result[6], result[7]))
            return scores
        except pymssql.Error as e:
            print("Error finding score by id:", e)

    def find_all(self):
        query = "SELECT * FROM score"
        try:
            self.connection.cursor.execute(query)
            results = self.connection.cursor.fetchall()
            scores = []
            for result in results:
                scores.append(
                    Score(result[0], result[1], result[2], result[3], result[4], result[5], result[6], result[7]))
            return scores
        except pymssql.Error as e:
            print("Error finding all scores:", e)

# 登录验证
class UserDao:
    def __init__(self, connection):
        self.connection = connection

    def find_by_id(self, id):
        query = "SELECT * FROM user_info WHERE user_id = %s"
        try:
            self.connection.cursor.execute(query, (id,))
            result = self.connection.cursor.fetchone()
            if result:
                return User2(result[0], result[1], result[2])
            return None
        except pymssql.Error as e:
            print("Error finding score by id:", e)

    def get_role(self, id):
        query = "SELECT * FROM user_role_info WHERE user_id = %s"
        try:
            self.connection.cursor.execute(query, id)
            result = self.connection.cursor.fetchone()
            return result[1]
        except pymssql.Error as e:
            print("Error finding user_role_info by id:", e)
    # def find_all(self):
    #     query = "SELECT * FROM score"
    #     try:
    #         self.connection.cursor.execute(query)
    #         results = self.connection.cursor.fetchall()
    #         scores = []
    #         for result in results:
    #             scores.append(
    #                 Score(result[0], result[1], result[2], result[3], result[4], result[5], result[6], result[7]))
    #         return scores
    #     except pymssql.Error as e:
    #         print("Error finding all scores:", e)

# 学科秘书


# DAO工厂类
class DAOFactory:
    server = 'localhost'
    user ='sa'
    password = '123456'
    database = 'education'
    def __init__(self, server, user, password, database):
        self.server = server
        self.user = user
        self.password = password
        self.database = database

    def getPrimarySubjectDAO(self):
        connection = DAOConnection(self.server, self.user, self.password, self.database)
        connection.connect()
        return PrimarySubjectDAO(connection)

    def getSecondarySubjectDAO(self):
        connection = DAOConnection(self.server, self.user, self.password, self.database)
        connection.connect()
        return SecondarySubjectDAO(connection)

    def getTutorDAO(self):
        connection = DAOConnection(self.server, self.user, self.password, self.database)
        connection.connect()
        return TutorDAO(connection)

    def getStudentDAO(self):
        connection = DAOConnection(self.server, self.user, self.password, self.database)
        connection.connect()
        return StudentDAO(connection)

    def getChoiceDAO(self):
        connection = DAOConnection(self.server, self.user, self.password, self.database)
        connection.connect()
        return ChoiceDAO(connection)

    def getScoreDAO(self):
        connection = DAOConnection(self.server, self.user, self.password, self.database)
        connection.connect()
        return ScoreDAO(connection)
    def getUserDao(self):
        connection = DAOConnection(self.server, self.user, self.password, self.database)
        connection.connect()
        return UserDao(connection)

# 测试数据库连接配置
server = 'localhost'
user ='sa'
password = '123456'
database = 'system11-7'

# 一级学科DAO测试
def test_primary_subject_dao():
    connection = DAOConnection(server, user, password, database)
    connection.connect()
    primary_subject_dao = PrimarySubjectDAO(connection)

    # 插入测试
    new_primary_subject = PrimarySubject(81200, '计算机科学与技术', '计算机科学与技术是一个计算机系统与网络兼顾的计算机学科宽口径专业', 27, 0, '工学方向')
    primary_subject_dao.insert(new_primary_subject)

    new_primary_subject = PrimarySubject(85400, '电子信息', '一门应用计算机等现代化技术进行电子信息控制和信息处理的学科', 88, 0, '工学')
    primary_subject_dao.insert(new_primary_subject)

    new_primary_subject = PrimarySubject(95136, '农业工程与信息技术', '农业机械化技术应用与推广、农业技术应用开发及推广', 14, 0, '工学')
    primary_subject_dao.insert(new_primary_subject)



    # 查询测试
    retrieved_primary_subject = primary_subject_dao.find_by_id(1)
    print(retrieved_primary_subject)

    # 更新测试
    retrieved_primary_subject.total_num = 120
    primary_subject_dao.update(retrieved_primary_subject)

    # 删除测试
    # primary_subject_dao.delete(1)

    connection.close()

# 二级学科DAO测试
def test_secondary_subject_dao():
    connection = DAOConnection(server, user, password, database)
    connection.connect()
    secondary_subject_dao = SecondarySubjectDAO(connection)

    # 插入测试
    new_secondary_subject = SecondarySubject(1, '大数据技术与人工智能', 81200, '人工智能领域学科', '工学', '思想政治理论', '英语一', '数学一', '计算机学科专业基础', '程序语言设计', '离散数学')
    secondary_subject_dao.insert(new_secondary_subject)

    # 查询测试
    retrieved_secondary_subject = secondary_subject_dao.find_by_id(1)
    print(retrieved_secondary_subject)

    # 更新测试
    retrieved_secondary_subject.name = '大数据技术与人工智能'
    secondary_subject_dao.update(retrieved_secondary_subject)

    # 删除测试
    # secondary_subject_dao.delete(1)

    connection.close()

# 导师DAO测试
def test_tutor_dao():
    connection = DAOConnection(server, user, password, database)
    connection.connect()
    tutor_dao = TutorDAO(connection)

    # 插入测试
    new_tutor = Tutor(1, '张导师', '教授', '010', '经验丰富的导师', 'zhangsan@example.com', '1234567890', 1)
    tutor_dao.insert(new_tutor)
    new_tutor = Tutor(2, '张导师', '教授', '010', '经验丰富的导师', 'zhangsan@example.com', '1234567890', 1)
    tutor_dao.insert(new_tutor)
    new_tutor = Tutor(3, '张导师', '教授', '010', '经验丰富的导师', 'zhangsan@example.com', '1234567890', 1)
    tutor_dao.insert(new_tutor)

    # 查询测试
    retrieved_tutor = tutor_dao.find_by_id(1)
    print(retrieved_tutor)

    # 更新测试
    retrieved_tutor.title = '副教授'
    tutor_dao.update(retrieved_tutor)

    # 删除测试
    # tutor_dao.delete(1)

    connection.close()

# 学生DAO测试
def test_student_dao():
    connection = DAOConnection(server, user, password, database)
    connection.connect()
    student_dao = StudentDAO(connection)

    # 插入测试
    new_student = Student(1, '李四', '1995-05-05', '123456789012345678', '北京', '计算机科学与技术', 'lisi@example.com', '01012345678', '清华大学', '双一流', 'Male', '优秀学生')
    student_dao.insert(new_student)

    # 查询测试
    retrieved_student = student_dao.find_by_id(1)
    print(retrieved_student)

    # 更新测试
    retrieved_student.name = '李五'
    student_dao.update(retrieved_student)

    # 删除测试
    # student_dao.delete(1)

    connection.close()

# 志愿DAO测试（假设已经有相应的ChoiceDAO类，这里只是示例，实际需要根据完整代码调整）
def test_choice_dao():
    connection = DAOConnection(server, user, password, database)
    connection.connect()
    choice_dao = ChoiceDAO(connection)  # 假设的ChoiceDAO类，实际需补充完整

    # 插入测试
    new_choice = Choice(1, 1, 1, 2, 3)  # 假设学生ID、导师ID等已存在，实际需根据情况调整
    choice_dao.insert(new_choice)

    # 查询测试
    retrieved_choice = choice_dao.find_by_id(1)
    print(retrieved_choice)

    # 更新测试（如果有合理的更新逻辑，这里只是示例）
    retrieved_choice.second_choice_instructor = 3
    choice_dao.update(retrieved_choice)

    # 删除测试
    choice_dao.delete(1)

    connection.close()

# 成绩DAO测试（假设已经有相应的ScoreDAO类，这里只是示例，实际需要根据完整代码调整）
def test_score_dao():
    connection = DAOConnection(server, user, password, database)
    connection.connect()
    score_dao = ScoreDAO(connection)  # 假设的ScoreDAO类，实际需补充完整

    # 插入测试
    new_score = Score(1, 1, 80, 85, 90, 95, 92)  # 假设学生ID等已存在，实际需根据情况调整
    score_dao.insert(new_score)

    # 查询测试
    retrieved_score = score_dao.find_by_id(1)
    print(retrieved_score)

    # 更新测试（如果有合理的更新逻辑，这里只是示例）
    retrieved_score.initial_score1 = 82
    score_dao.update(retrieved_score)

    # 删除测试
    score_dao.delete(1)

    connection.close()

if __name__ == "__main__":
    # test_primary_subject_dao()
    # test_secondary_subject_dao()
    # test_tutor_dao()
    # test_student_dao()
    # test_choice_dao()  # 注意：需确保ChoiceDAO类完整实现
    # test_score_dao()  # 注意：需确保ScoreDAO类完整实现
    # 使用DAO工厂获取学生DAO对象
    factory = DAOFactory(server, user, password, database)
    primary_subject_dao = factory.getPrimarySubjectDAO()
    primary_subjects = primary_subject_dao.find_all()


    secondary_suject_dao = factory.getSecondarySubjectDAO()
    secondary_subjects = secondary_suject_dao.find_all()


    subject_data = {}
    for primary_subject in primary_subjects:
        subject_data[primary_subject.id] = {
            'name': primary_subject.name,
            'description': primary_subject.description,
            'secondary_subjects': []
        }
    for secondary_subject in secondary_subjects:
        if secondary_subject.parent_id in subject_data:
            subject_data[secondary_subject.parent_id]['secondary_subjects'].append({
                'id': secondary_subject.id,
                'name': secondary_subject.name,
                'description': secondary_subject.description
            })
    print(subject_data)